package com.caopeng.javaee.dao;

import com.caopeng.javaee.entity.student;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.io.Serializable;

@Repository
public interface StudentJpa extends JpaRepository<student,Integer>, Serializable, JpaSpecificationExecutor<student> {
    //japspecification复杂查询 使用
    student findByAccountAndPassword(String account,String password);
    student findByAccountAndPhone(String account,String phone);
    student findByAccountAndName(String  account,String name);
    student findById(int id);
    @Query(value = "SELECT * FROM student as a where not EXISTS(select * from course_student as b where  b.course_id=:courseid And a.id=b.student_id) and a.`name` LIKE CONCAT('%',:studentname,'%')",
            countQuery ="SELECT COUNT(*) FROM student  as a where not EXISTS(select * from course_student as b where  b.course_id=:courseid And a.id=b.student_id)",
            nativeQuery = true)
    Page<student> ScourseselectStudent(@Param("courseid") String courseid,@Param("studentname") String studentname,Pageable pageable);
}
